package com.example;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import java.io.File;
import java.io.FileOutputStream;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;

public class ExportExcelDemo {

    private FileOutputStream fos;

    private Workbook workbook;

    private Sheet sheet;

    @Before
    public void init() throws Exception{
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HHmmss");
        String userHome = System.getProperty("user.home");
        String parentDir = userHome + "/excel_dir/";
        String fileName = "测试Excel" + sdf.format(new Date());
        String suffix = ".xls";
        File file = new File(parentDir+fileName+suffix);
        if(!file.getParentFile().exists()){
            file.getParentFile().mkdirs();
        }
        fos = new FileOutputStream(file);
        workbook = new HSSFWorkbook();
        sheet = workbook.createSheet();
    }

    public HSSFCellStyle getCellStyle(Workbook workbook){
        CellStyle cellStyle = workbook.createCellStyle();

        //设置单元格四周边框线条粗细，接收BorderStyle类型(旧版本获取样例HSSFCellStyle.BorderStyle.THIN)
        cellStyle.setBorderTop(BorderStyle.MEDIUM);//实线
        cellStyle.setBorderBottom(BorderStyle.DOUBLE);//双实线
        cellStyle.setBorderLeft(BorderStyle.THICK);//粗实线
        cellStyle.setBorderRight(BorderStyle.DASH_DOT);//虚线

        //设置边框的颜色，接收short类型
        cellStyle.setTopBorderColor(IndexedColors.ORANGE.getIndex());
        cellStyle.setBottomBorderColor(IndexedColors.BLUE.getIndex());
        cellStyle.setLeftBorderColor(IndexedColors.GREEN.getIndex());
        cellStyle.setRightBorderColor(IndexedColors.RED.getIndex());

        return null;
    }

    /**
     * 测试单元格支持的数据类型
     */
    @Test
    public void testCellDataType(){
        Row row = sheet.createRow(0);

        CellStyle cellStyle = workbook.createCellStyle();
        CreationHelper creationHelper = workbook.getCreationHelper();
        cellStyle.setDataFormat(creationHelper.createDataFormat().getFormat("yyyy-MM-dd HH:mm:ss"));

        Cell cell0 = row.createCell(0);
        cell0.setCellValue(new Date());
        cell0.setCellStyle(cellStyle);

        Cell cell1 = row.createCell(1);
        cell1.setCellValue(Calendar.getInstance());
        cell1.setCellStyle(cellStyle);

        Cell cell2 = row.createCell(2);
        cell2.setCellValue(0.1);

        Cell cell3 = row.createCell(3);
        cell3.setCellValue("字符串");

        //TRUE
        Cell cell4 = row.createCell(4);
        cell4.setCellValue(true);

        //-1
        Cell cell5 = row.createCell(5);
        cell5.setCellValue(HSSFCell.ENCODING_UNCHANGED);

        //255
        Cell cell6 = row.createCell(6);
        cell6.setCellValue(HSSFCell.LAST_COLUMN_NUMBER);

    }

    @Test
    public void cellStyleTest(){

    }

    @After
    public void destory() throws Exception{
        workbook.write(fos);
        if(null != fos){
            fos.close();
        }
    }

}
